USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_elapsed_time') BEGIN
	DROP FUNCTION tsql.fn_elapsed_time
	PRINT '<<< DROP FUNCTION tsql.fn_elapsed_time - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*================================================================================================
Name:			tsql.fn_elapsed_time
Type:			User defined function 
Owner:					
Description:	returns duration in xxxd HH:mm:ss format (i.e. 1d 13:24:36)
Parameters:		@start_date = date when event began, default is GETDATE()
				@end_date = date when ev en ended, GETDATE() is default 
Dependens on:  	
Usage:			SELECT Lib.tsql.fn_elapsed_time('1/1/2012 11:54:12am',DEFAULT) 
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_elapsed_time&referringTitle=Documentation		
Version:		20120829
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
Note*:			CTRL + click to follow the links above
--------------------------------------------------------------------------------------------------
History:		08/29/2012 - Adrian E Dudau - Function created
================================================================================================*/
CREATE FUNCTION [tsql].fn_elapsed_time(
	  @start_date DATETIME = NULL
	, @end_date DATETIME = NULL
	)
RETURNS NVARCHAR(50)  
AS 
BEGIN /*FUNCTION*/
DECLARE @retval NVARCHAR(50) = '' 

SET @start_date = ISNULL(@start_date, GETDATE())
SET @end_date = ISNULL(@end_date, GETDATE())

IF @start_date > @end_date  
	SELECT @retval = '-' + CASE WHEN ABS(DATEDIFF(SECOND,@end_date, @start_date)) < 86400 THEN '0' ELSE CAST(DATEDIFF(DAY,@end_date , @start_date) AS VARCHAR) END   
	 + 'd ' + CONVERT(VARCHAR,DATEADD(SECOND,DATEDIFF(SECOND,@end_date, @start_date), 0), 108)
ELSE
	SELECT @retval = CASE WHEN ABS(DATEDIFF(SECOND,@start_date, @end_date)) < 86400 THEN '0' ELSE CAST(DATEDIFF(DAY,@start_date , @end_date) AS VARCHAR) END 
	+ 'd ' + CONVERT(VARCHAR,DATEADD(SECOND,DATEDIFF(SECOND,@start_date, @end_date), 0), 108)

	RETURN @retval 
END /*FUNCTION*/
GO
---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_elapsed_time') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_elapsed_time - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_elapsed_time - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

